package com.bsoft.gol.hcs.specialist.dao;

import ctd.persistence.support.hibernate.HibernateSupportDelegateDAO;
import ctd.persistence.support.hibernate.template.AbstractHibernateStatelessResultAction;
import ctd.persistence.support.hibernate.template.HibernateSessionTemplate;
import ctd.persistence.support.hibernate.template.HibernateStatelessResultAction;
import ctd.util.converter.ConversionUtils;
import org.hibernate.Query;
import org.hibernate.StatelessSession;
import org.hibernate.transform.Transformers;
import org.springframework.util.StringUtils;
import com.bsoft.gol.hcs.specialist.bean.request.*;
import com.bsoft.gol.hcs.specialist.entity.FollowUpRecords;
import com.bsoft.gol.hcs.specialist.entity.FusTeamChronicGroupRecordEntity;
import com.bsoft.gol.hcs.specialist.service.impl.QueryByExtendedRequest;
import com.bsoft.gol.hcs.specialist.service.impl.QueryByStagesRequest;
import com.bsoft.gol.hcs.utils.CommonUtils;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author tongtp
 * @version v0.1
 * @className ProduceExecutionListDAO
 * @description 生成随访计划DAO
 * @create 2020-07-29 9:52
 **/
public abstract class ProduceExecutionListDAO extends HibernateSupportDelegateDAO<FollowUpRecords> {

	/**
	 * @param diseaseTypeId
	 * @throws
	 * @description: 查询未被特定随访路径处理过且有效的入组记录
	 * @return: java.util.List<specialist.entity.FusTeamChronicGroupRecordEntity>
	 * @author: tongtp
	 * @date: 2020/7/28 10:37
	 */
	public List<FusTeamChronicGroupRecordEntity> queryByChronicDiseaseIdAndUnHandle(final Long diseaseTypeId,
																					final Long routeDetailId) {
		HibernateStatelessResultAction<List<FusTeamChronicGroupRecordEntity>> action =
				new AbstractHibernateStatelessResultAction<List<FusTeamChronicGroupRecordEntity>>() {
					@Override
					public void execute(StatelessSession ss) throws Exception {
						StringBuilder sqlBuilder = new StringBuilder();
						sqlBuilder.append("SELECT " +
								"a.team_chronic_group_id AS teamChronicGroupId, " +
								"a.patient_id AS patientId, " +
								"a.person_id AS personId, " +
								"a.person_name AS personName, " +
								"a.person_sex AS personSex, " +
								"a.age AS age, " +
								"a.telephone AS telephone, " +
								"a.card_type AS cardType, " +
								"a.card_number AS cardNumber, " +
								"a.patient_medical_card_type AS patientMedicalCardType, " +
								"a.patient_medical_card_number AS patientMedicalCardNumber, " +
								"a.organization_id AS organizationId, " +
								"a.organization_name AS organizationName, " +
								"a.department_id AS departmentId, " +
								"a.department_name AS departmentName, " +
								"a.doctor_id AS doctorId, " +
								"a.doc_id AS docId, " +
								"a.doctor_name AS doctorName, " +
								"a.chronic_disease_id AS chronicDiseaseId, " +
								"a.chronic_disease_name AS chronicDiseaseName, " +
								"a.medical_team_id AS medicalTeamId, " +
								"a.medical_team_name AS medicalTeamName, " +
								"a.apply_status AS applyStatus, " +
								"a.apply_person_id AS applyPersonId, " +
								"a.apply_person_name AS applyPersonName, " +
								"a.apply_time AS applyTime, " +
								"a.audit_person_id AS auditPersonId, " +
								"a.audit_person_name AS auditPersonName, " +
								"a.audit_time AS auditTime, " +
								"a.audit_opinion AS auditOpinion, " +
								"a.apply_type AS applyType, " +
								"a.out_person_id AS outPersonId, " +
								"a.out_time AS outTime " +
								"FROM " +
								"fus_team_chronic_group_record AS a " +
								"LEFT JOIN follow_up_plan_produce_record AS b ON a.team_chronic_group_id = b" +
								".team_chronic_group_id AND b.route_detail_id = :routeDetailId " +
								"WHERE " +
								"a.apply_status = '1' AND " +
								"a.chronic_disease_id = :diseaseTypeId AND " +
								"b.route_detail_id IS NULL");

						Query query = ss.createSQLQuery(sqlBuilder.toString());
						query.setParameter("diseaseTypeId", diseaseTypeId);
						query.setParameter("routeDetailId", routeDetailId);
						query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

						List<Map> list = query.list();
						List<FusTeamChronicGroupRecordEntity> voList = new ArrayList<>();
						if (CommonUtils.isNotEmpty(list)) {
							for (Map map : list) {
								voList.add(ConversionUtils.convert(map, FusTeamChronicGroupRecordEntity.class));
							}
						} else {
							voList = new ArrayList<>();
						}
						setResult(voList);
					}
				};
		HibernateSessionTemplate.instance().execute(action);
		return action.getResult();
	}

	/**
	 * @param chronicDiseaseId
	 * @throws
	 * @description: 查询未被特定随访路径处理过且有效的入组记录
	 * @return: java.util.List<specialist.entity.FusTeamChronicGroupRecordEntity>
	 * @author: tongtp
	 * @date: 2020/7/28 10:37
	 */
	public List<FusTeamChronicGroupRecordEntity> queryByChronicDiseaseId(final long chronicDiseaseId,
																		 final Long routeDetailId) {
		HibernateStatelessResultAction<List<FusTeamChronicGroupRecordEntity>> action =
				new AbstractHibernateStatelessResultAction<List<FusTeamChronicGroupRecordEntity>>() {
					@Override
					public void execute(StatelessSession ss) throws Exception {
						StringBuilder sqlBuilder = new StringBuilder();
						sqlBuilder.append("SELECT " +
								"a.team_chronic_group_id AS teamChronicGroupId, " +
								"a.patient_id AS patientId, " +
								"a.person_id AS personId, " +
								"a.person_name AS personName, " +
								"a.person_sex AS personSex, " +
								"a.age AS age, " +
								"a.telephone AS telephone, " +
								"a.card_type AS cardType, " +
								"a.card_number AS cardNumber, " +
								"a.patient_medical_card_type AS patientMedicalCardType, " +
								"a.patient_medical_card_number AS patientMedicalCardNumber, " +
								"a.organization_id AS organizationId, " +
								"a.organization_name AS organizationName, " +
								"a.department_id AS departmentId, " +
								"a.department_name AS departmentName, " +
								"a.doctor_id AS doctorId, " +
								"a.doc_id AS docId, " +
								"a.doctor_name AS doctorName, " +
								"a.chronic_disease_id AS chronicDiseaseId, " +
								"a.chronic_disease_name AS chronicDiseaseName, " +
								"a.medical_team_id AS medicalTeamId, " +
								"a.medical_team_name AS medicalTeamName, " +
								"a.apply_status AS applyStatus, " +
								"a.apply_person_id AS applyPersonId, " +
								"a.apply_person_name AS applyPersonName, " +
								"a.apply_time AS applyTime, " +
								"a.audit_person_id AS auditPersonId, " +
								"a.audit_person_name AS auditPersonName, " +
								"a.audit_time AS auditTime, " +
								"a.audit_opinion AS auditOpinion, " +
								"a.apply_type AS applyType, " +
								"a.out_person_id AS outPersonId, " +
								"a.out_time AS outTime " +
								"FROM " +
								"fus_team_chronic_group_record AS a " +
								"LEFT JOIN follow_up_plan_produce_record AS b ON a.team_chronic_group_id = b" +
								".team_chronic_group_id " +
								" AND b.route_detail_id = :routeDetailId " +
								"WHERE " +
								"a.apply_status = '1' AND " +
								"a.chronic_disease_id = :chronicDiseaseId AND " +
								"b.route_detail_id IS NULL");

						Query query = ss.createSQLQuery(sqlBuilder.toString());
						query.setParameter("chronicDiseaseId", chronicDiseaseId);
						query.setParameter("routeDetailId", routeDetailId);
						query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

						List<Map> list = query.list();
						List<FusTeamChronicGroupRecordEntity> voList = new ArrayList<>();
						if (CommonUtils.isNotEmpty(list)) {
							for (Map map : list) {
								voList.add(ConversionUtils.convert(map, FusTeamChronicGroupRecordEntity.class));
							}
						} else {
							voList = new ArrayList<>();
						}
						setResult(voList);
					}
				};
		HibernateSessionTemplate.instance().execute(action);
		return action.getResult();
	}

	/**
	 * @param request
	 * @throws
	 * @description: 查询对应病种最晚填写时间的出院时间
	 * @return: java.util.List<java.lang.Long>
	 * @author: tongtp
	 * @date: 2020/7/23 20:24
	 */
	public Date queryDischargeTime(final QueryDischargeTimeRequest request) {
		HibernateStatelessResultAction<Date> action =
				new AbstractHibernateStatelessResultAction<Date>() {
					@Override
					public void execute(StatelessSession ss) throws Exception {
						StringBuilder sqlBuilder = new StringBuilder();
						sqlBuilder.append("select a.discharge_time AS dischargeTime from fus_answer_in_hospital_info" +
								" " +
								"a" +
								" where (");
						sqlBuilder.append(" a.patient_id = :patientId ");
						if (StringUtils.hasText(request.getCardType()) || StringUtils.hasText(request.getCardNumber())) {
							sqlBuilder.append(" OR (a.card_type = :cardType and a.card_number = :cardNumber) ");
						}
						if (request.getPatientMedicalCardType() != null || StringUtils.hasText(request.getPatientMedicalCardNumber())) {
							sqlBuilder.append("  OR (a.patient_medical_card_type = :patientMedicalCardType AND a" +
									".patient_medical_card_number = :patientMedicalCardNumber) ");
						}
						sqlBuilder.append(") AND a.disease_type_id = :diseaseTypeId ORDER BY a.answer_time DESC " +
								"LIMIT 1");
						Query query = ss.createSQLQuery(sqlBuilder.toString());
						query.setParameter("diseaseTypeId", request.getDiseaseTypeId());
						query.setParameter("patientId", request.getPatientId());
						if (StringUtils.hasText(request.getCardType()) || StringUtils.hasText(request.getCardNumber())) {
							query.setParameter("cardType", request.getCardType());
							query.setParameter("cardNumber", request.getCardNumber());
						}
						if (request.getPatientMedicalCardType() != null || StringUtils.hasText(request.getPatientMedicalCardNumber())) {
							query.setParameter("patientMedicalCardType", request.getPatientMedicalCardType());
							query.setParameter("patientMedicalCardNumber", request.getPatientMedicalCardNumber());
						}

						List list = query.list();
						Date result = null;
						if (CommonUtils.isNotEmpty(list)) {
							result = (Date) list.get(0);
						}
						setResult(result);
					}
				};
		HibernateSessionTemplate.instance().execute(action);
		return action.getResult();
	}

	/**
	 * @param request
	 * @throws
	 * @description: 查询对应病种最晚填写时间的门诊就诊时间
	 * @return: java.util.List<java.lang.Long>
	 * @author: tongtp
	 * @date: 2020/7/23 20:24
	 */
	public Date queryVisitTime(final QueryVisitTimeRequest request) {
		HibernateStatelessResultAction<Date> action =
				new AbstractHibernateStatelessResultAction<Date>() {
					@Override
					public void execute(StatelessSession ss) throws Exception {
						StringBuilder sqlBuilder = new StringBuilder();
						sqlBuilder.append("select a.visit_time AS visitTime from fus_answer_outpatient_info a where " +
								"(");
						sqlBuilder.append(" a.patient_id = :patientId ");
						if (StringUtils.hasText(request.getCardType()) || StringUtils.hasText(request.getCardNumber())) {
							sqlBuilder.append(" OR (a.card_type = :cardType and a.card_number = :cardNumber) ");
						}
						if (request.getPatientMedicalCardType() != null || StringUtils.hasText(request.getPatientMedicalCardNumber())) {
							sqlBuilder.append("  OR (a.patient_medical_card_type = :patientMedicalCardType AND a" +
									".patient_medical_card_number = :patientMedicalCardNumber) ");
						}
						sqlBuilder.append(") AND a.disease_type_id = :diseaseTypeId ORDER BY a.answer_time DESC " +
								"LIMIT 1");
						Query query = ss.createSQLQuery(sqlBuilder.toString());
						query.setParameter("diseaseTypeId", request.getDiseaseTypeId());
						query.setParameter("patientId", request.getPatientId());
						if (StringUtils.hasText(request.getCardType()) || StringUtils.hasText(request.getCardNumber())) {
							query.setParameter("cardType", request.getCardType());
							query.setParameter("cardNumber", request.getCardNumber());
						}
						if (request.getPatientMedicalCardType() != null || StringUtils.hasText(request.getPatientMedicalCardNumber())) {
							query.setParameter("patientMedicalCardType", request.getPatientMedicalCardType());
							query.setParameter("patientMedicalCardNumber", request.getPatientMedicalCardNumber());
						}

						List list = query.list();
						Date result = null;
						if (CommonUtils.isNotEmpty(list)) {
							result = (Date) list.get(0);
						}
						setResult(result);
					}
				};
		HibernateSessionTemplate.instance().execute(action);
		return action.getResult();
	}

	/**
	 * @param request
	 * @throws
	 * @description: 查询对应手术和病种最晚填写时间的手术时间
	 * @return: java.util.List<java.lang.Long>
	 * @author: tongtp
	 * @date: 2020/7/23 20:24
	 */
	public Date queryOperativeTime(final QueryOperativeTimeRequest request) {
		HibernateStatelessResultAction<Date> action =
				new AbstractHibernateStatelessResultAction<Date>() {
					@Override
					public void execute(StatelessSession ss) throws Exception {
						StringBuilder sqlBuilder = new StringBuilder();
						sqlBuilder.append("select a.operative_time AS operativeTime from fus_answer_operative_info a" +
								" where 1 = 1 ");
						if (StringUtils.hasText(request.getOperativeCode())) {
							sqlBuilder.append(" and a.operative_code = :operativeCode");
						}
						sqlBuilder.append(" and ( a.patient_id = :patientId ");
						if (StringUtils.hasText(request.getCardType()) || StringUtils.hasText(request.getCardNumber())) {
							sqlBuilder.append(" OR (a.card_type = :cardType and a.card_number = :cardNumber) ");
						}
						if (request.getPatientMedicalCardType() != null || StringUtils.hasText(request.getPatientMedicalCardNumber())) {
							sqlBuilder.append("  OR (a.patient_medical_card_type = :patientMedicalCardType AND a" +
									".patient_medical_card_number = :patientMedicalCardNumber) ");
						}
						sqlBuilder.append(") AND a.disease_type_id = :diseaseTypeId ORDER BY a.answer_time DESC " +
								"LIMIT 1");
						Query query = ss.createSQLQuery(sqlBuilder.toString());
						query.setParameter("diseaseTypeId", request.getDiseaseTypeId());
						if (StringUtils.hasText(request.getOperativeCode())) {
							query.setParameter("operativeCode", request.getOperativeCode());
						}
						query.setParameter("patientId", request.getPatientId());
						if (StringUtils.hasText(request.getCardType()) || StringUtils.hasText(request.getCardNumber())) {
							query.setParameter("cardType", request.getCardType());
							query.setParameter("cardNumber", request.getCardNumber());
						}
						if (request.getPatientMedicalCardType() != null || StringUtils.hasText(request.getPatientMedicalCardNumber())) {
							query.setParameter("patientMedicalCardType", request.getPatientMedicalCardType());
							query.setParameter("patientMedicalCardNumber", request.getPatientMedicalCardNumber());
						}

						List list = query.list();
						Date result = null;
						if (CommonUtils.isNotEmpty(list)) {
							result = (Date) list.get(0);
						}
						setResult(result);
					}
				};
		HibernateSessionTemplate.instance().execute(action);
		return action.getResult();
	}

	/**
	 * 根据病种id查询该病种有效的入组患者的病人id
	 *
	 * @param chronicDiseaseId
	 * @return java.util.List<java.lang.String>
	 * @throws
	 * @author tongtp
	 * @date 2022/6/14 15:15
	 */
	public List<String> queryPatientIdByChronicDiseaseId(final long chronicDiseaseId) {
		HibernateStatelessResultAction<List<String>> action =
				new AbstractHibernateStatelessResultAction<List<String>>() {
					@Override
					public void execute(StatelessSession ss) throws Exception {
						StringBuilder sqlBuilder = new StringBuilder();
						sqlBuilder.append("SELECT DISTINCT a.patient_id AS patientId ");
						sqlBuilder.append(" FROM fus_team_chronic_group_record AS a ");
						sqlBuilder.append(" WHERE a.apply_status = '1' AND ");
						sqlBuilder.append(" a.chronic_disease_id = :chronicDiseaseId and a.patient_id is not null ");

						Query query = ss.createSQLQuery(sqlBuilder.toString());
						query.setParameter("chronicDiseaseId", chronicDiseaseId);

						List list = query.list();
						List<String> voList;
						if (CommonUtils.isNotEmpty(list)) {
							voList = list;
						} else {
							voList = new ArrayList<>();
						}
						setResult(voList);
					}
				};
		HibernateSessionTemplate.instance().execute(action);
		return action.getResult();
	}

	/**
	 * @param request
	 * @throws
	 * @description: 根据手术和条件是查询
	 * @return: java.util.List<java.lang.Long>
	 * @author: tongtp
	 * @date: 2020/7/23 20:24
	 */
	public List<Long> queryByOperativeAndYes(final QueryByOperativeRequest request, final Byte isLoopExecute) {
		HibernateStatelessResultAction<List<Long>> action =
				new AbstractHibernateStatelessResultAction<List<Long>>() {
					@Override
					public void execute(StatelessSession ss) throws Exception {
						StringBuilder sqlBuilder = new StringBuilder();
						sqlBuilder.append("select m.team_chronic_group_id AS teamChronicGroupId from (");
						sqlBuilder.append("select a.team_chronic_group_id,b.report_id from " +
								"fus_team_chronic_group_record a INNER JOIN fus_answer_operative_info b on (a" +
								".person_id" +
								" = b.patient_id OR (a.card_type = b.card_type and a.card_number = b.card_number)) " +
								"and" +
								" a.chronic_disease_id = b.disease_type_id " +
								"where a.chronic_disease_id = :diseaseTypeId and b.operative_code = :operativeCode ");
						sqlBuilder.append(" and a.audit_time <= b.answer_time ");
						sqlBuilder.append(") AS  m ");
						//循环执行机制 0 不执行 1 执行
						if (isLoopExecute != null && isLoopExecute == 1) {
							sqlBuilder.append(" LEFT JOIN fus_answer_route_record n " +
									"on m.report_id = n.report_id" +
									" AND n.route_detail_id = :routeDetailId WHERE n.route_detail_id IS NULL");
						} else {
							sqlBuilder.append(" LEFT JOIN follow_up_plan_produce_record n " +
									"on m.team_chronic_group_id = n.team_chronic_group_id" +
									" AND n.route_detail_id = :routeDetailId WHERE n.route_detail_id IS NULL");
						}
						Query query = ss.createSQLQuery(sqlBuilder.toString());
						query.setParameter("diseaseTypeId", request.getDiseaseTypeId());
						query.setParameter("operativeCode", request.getOperativeCode());
						query.setParameter("routeDetailId", request.getRouteDetailId());

						List list = query.list();
						List<Long> voList;
						if (CommonUtils.isNotEmpty(list)) {
							voList = list;
						} else {
							voList = new ArrayList<>();
						}
						setResult(voList);
					}
				};
		HibernateSessionTemplate.instance().execute(action);
		return action.getResult();
	}

	/**
	 * @param request
	 * @throws
	 * @description: 根据手术和条件否查询
	 * @return: java.util.List<java.lang.Long>
	 * @author: tongtp
	 * @date: 2020/7/23 20:24
	 */
	public List<Long> queryByOperativeAndNO(final QueryByOperativeRequest request, final Byte isLoopExecute) {
		HibernateStatelessResultAction<List<Long>> action =
				new AbstractHibernateStatelessResultAction<List<Long>>() {
					@Override
					public void execute(StatelessSession ss) throws Exception {
						StringBuilder sqlBuilder = new StringBuilder();
						sqlBuilder.append("select m.team_chronic_group_id AS teamChronicGroupId from (");
						sqlBuilder.append("select c.team_chronic_group_id,d.report_id from  " +
								"fus_team_chronic_group_record c LEFT JOIN ( " +
								"select a.team_chronic_group_id,b.report_id from  " +
								"fus_team_chronic_group_record a INNER JOIN fus_answer_operative_info b" +
								" on (a.person_id " +
								" = b.patient_id OR (a.card_type = b.card_type and a.card_number = b" +
								".card_number)) " +
								"and a.chronic_disease_id = b.disease_type_id where a.chronic_disease_id = " +
								":diseaseTypeId AND b.operative_code =" +
								" :operativeCode and a.audit_time <= b.answer_time ) AS d on c.team_chronic_group_id" +
								" " +
								"= d.team_chronic_group_id where c" +
								".chronic_disease_id = :diseaseTypeId AND d" +
								".team_chronic_group_id IS NULL");
						sqlBuilder.append(") AS  m ");
						//循环执行机制 0 不执行 1 执行
						if (isLoopExecute != null && isLoopExecute == 1) {
							sqlBuilder.append(" LEFT JOIN fus_answer_route_record n " +
									"on m.report_id = n.report_id" +
									" AND n.route_detail_id = :routeDetailId WHERE n.route_detail_id IS NULL");
						} else {
							sqlBuilder.append(" LEFT JOIN follow_up_plan_produce_record n " +
									"on m.team_chronic_group_id = n.team_chronic_group_id" +
									" AND n.route_detail_id = :routeDetailId WHERE n.route_detail_id IS NULL");
						}

						Query query = ss.createSQLQuery(sqlBuilder.toString());
						query.setParameter("diseaseTypeId", request.getDiseaseTypeId());
						query.setParameter("operativeCode", request.getOperativeCode());
						query.setParameter("routeDetailId", request.getRouteDetailId());

						List list = query.list();
						List<Long> voList;
						if (CommonUtils.isNotEmpty(list)) {
							voList = list;
						} else {
							voList = new ArrayList<>();
						}
						setResult(voList);
					}
				};
		HibernateSessionTemplate.instance().execute(action);
		return action.getResult();
	}

	/**
	 * @param request
	 * @throws
	 * @description: 根据指标查询
	 * @return: java.util.List<java.lang.Long>
	 * @author: tongtp
	 * @date: 2020/7/23 19:49
	 */
	public List<Long> queryByIndex(final QueryByIndexRequest request, final Byte isLoopExecute) {
		HibernateStatelessResultAction<List<Long>> action =
				new AbstractHibernateStatelessResultAction<List<Long>>() {
					@Override
					public void execute(StatelessSession ss) throws Exception {
						//因素内容性质类型，1定量，2定性 3 文本,因素类型为关联指标必填
						boolean qualitative = request.getFactorNatureType() == (byte) 2;
						StringBuilder sqlBuilder = new StringBuilder();
						sqlBuilder.append("select m.team_chronic_group_id AS teamChronicGroupId from (");
						sqlBuilder.append("select a.team_chronic_group_id,b.report_id from " +
								"fus_team_chronic_group_record a INNER" +
								" " +
								"JOIN fus_answer_check_report_info b on (a.person_id = b.patient_id OR (a.card_type " +
								"=" +
								" " +
								"b.card_type and a.card_number = b.card_number)) and a.chronic_disease_id = b" +
								".disease_type_id where a.chronic_disease_id = :diseaseTypeId and b.item_id = " +
								":itemId" +
								" ");
						//因素内容性质类型，1定量，2定性 3 文本,因素类型为关联指标必填
						if (qualitative) {
							sqlBuilder.append(" and b.item_content = :itemContent1 and a.audit_time <= b.answer_time" +
									" ");
						} else {
							sqlBuilder.append(" and b.item_content >= :itemContent1 and b.item_content <= " +
									":itemContent2 and a.audit_time <= b.answer_time ");
						}
						sqlBuilder.append(" UNION ");
						sqlBuilder.append("select a.team_chronic_group_id,b.report_id from " +
								"fus_team_chronic_group_record a INNER" +
								" " +
								"JOIN fus_answer_inspection_report_info b on (a.person_id = b.patient_id OR (a" +
								".card_type = b.card_type and a.card_number = b.card_number)) and a" +
								".chronic_disease_id" +
								" = b.disease_type_id where a.chronic_disease_id = :diseaseTypeId and b.item_id = " +
								":itemId");
						//因素内容性质类型，1定量，2定性 3 文本,因素类型为关联指标必填
						if (qualitative) {
							sqlBuilder.append(" and b.result = :itemContent1 and a.audit_time <= b.answer_time ");
						} else {
							sqlBuilder.append(" and b.result >= :itemContent1  and b.result <= :itemContent2 and a" +
									".audit_time <= b.answer_time ");
						}

						//因为结果有字符串、整数、小数等多种情况，所以在下面，将结果值转成整数和小数，如果能转成功，则执行对应的比较SQL
						//只有定量才会需要处理这种情况，定性不需要
						Long longItemContent1 = null;
						Long longItemContent2 = null;
						Double doubleItemContent1 = null;
						Double doubleItemContent2 = null;
						if (!qualitative) {
							try {
								longItemContent1 = Long.valueOf(request.getFactorResultOne());
								longItemContent2 = Long.valueOf(request.getFactorResultTwo());
							} catch (Exception e) {
								longItemContent1 = null;
								longItemContent2 = null;
							}
							if (longItemContent1 != null && longItemContent2 != null) {
								sqlBuilder.append(" UNION ");
								sqlBuilder.append("select a.team_chronic_group_id,b.report_id from " +
										"fus_team_chronic_group_record" +
										" " +
										"a" +
										" " +
										"INNER" +
										" " +
										"JOIN fus_answer_check_report_info b on (a.person_id = b.patient_id OR (a" +
										".card_type " +
										"=" +
										" " +
										"b.card_type and a.card_number = b.card_number)) and a.chronic_disease_id = " +
										"b" +
										".disease_type_id where a.chronic_disease_id = :diseaseTypeId and b.item_id " +
										"=" +
										" " +
										":itemId" +
										" ");

								sqlBuilder.append(" and b.item_content >= :longItemContent1 and b.item_content <= " +
										":longItemContent2 and a.audit_time <= b.answer_time ");
								sqlBuilder.append(" UNION ");
								sqlBuilder.append("select a.team_chronic_group_id,b.report_id from " +
										"fus_team_chronic_group_record" +
										" " +
										"a" +
										" " +
										"INNER" +
										" " +
										"JOIN fus_answer_inspection_report_info b on (a.person_id = b.patient_id OR " +
										"(a" +
										".card_type = b.card_type and a.card_number = b.card_number)) and a" +
										".chronic_disease_id" +
										" = b.disease_type_id where a.chronic_disease_id = :diseaseTypeId and b" +
										".item_id " +
										"=" +
										" " +
										":itemId");
								sqlBuilder.append(" and b.result >= :longItemContent1  and b.result <= " +
										":longItemContent2 and" +
										" " +
										"a" +
										".audit_time <= b.answer_time ");
							}
							try {
								doubleItemContent1 = Double.valueOf(request.getFactorResultOne());
								doubleItemContent2 = Double.valueOf(request.getFactorResultTwo());
							} catch (Exception e) {
								doubleItemContent1 = null;
								doubleItemContent2 = null;
							}
							if (doubleItemContent1 != null && doubleItemContent2 != null) {
								sqlBuilder.append(" UNION ");
								sqlBuilder.append("select a.team_chronic_group_id,b.report_id from " +
										"fus_team_chronic_group_record" +
										" " +
										"a" +
										" " +
										"INNER" +
										" " +
										"JOIN fus_answer_check_report_info b on (a.person_id = b.patient_id OR (a" +
										".card_type " +
										"=" +
										" " +
										"b.card_type and a.card_number = b.card_number)) and a.chronic_disease_id = " +
										"b" +
										".disease_type_id where a.chronic_disease_id = :diseaseTypeId and b.item_id " +
										"=" +
										" " +
										":itemId" +
										" ");

								sqlBuilder.append(" and b.item_content >= :bigDecimalItemContent1 and b.item_content" +
										" " +
										"<= " +
										":bigDecimalItemContent2 and a.audit_time <= b.answer_time ");
								sqlBuilder.append(" UNION ");
								sqlBuilder.append("select a.team_chronic_group_id,b.report_id from " +
										"fus_team_chronic_group_record" +
										" " +
										"a" +
										" " +
										"INNER" +
										" " +
										"JOIN fus_answer_inspection_report_info b on (a.person_id = b.patient_id OR " +
										"(a" +
										".card_type = b.card_type and a.card_number = b.card_number)) and a" +
										".chronic_disease_id" +
										" = b.disease_type_id where a.chronic_disease_id = :diseaseTypeId and b" +
										".item_id " +
										"=" +
										" " +
										":itemId");
								sqlBuilder.append(" and b.result >= :bigDecimalItemContent1  and b.result <= " +
										":bigDecimalItemContent2 and" +
										" " +
										"a" +
										".audit_time <= b.answer_time ");
							}
						}


						sqlBuilder.append(") AS  m ");
						//循环执行机制 0 不执行 1 执行
						if (isLoopExecute != null && isLoopExecute == 1) {
							sqlBuilder.append(" LEFT JOIN fus_answer_route_record n " +
									"on m.report_id = n.report_id" +
									" AND n.route_detail_id = :routeDetailId WHERE n.route_detail_id IS NULL");
						} else {
							sqlBuilder.append(" LEFT JOIN follow_up_plan_produce_record n " +
									"on m.team_chronic_group_id = n.team_chronic_group_id" +
									" AND n.route_detail_id = :routeDetailId WHERE n.route_detail_id IS NULL");
						}
						Query query = ss.createSQLQuery(sqlBuilder.toString());
						query.setParameter("diseaseTypeId", request.getDiseaseTypeId());
						query.setParameter("itemId", request.getItemId());
						query.setParameter("routeDetailId", request.getRouteDetailId());
						query.setParameter("itemContent1", request.getFactorResultOne());
						if (!qualitative) {
							query.setParameter("itemContent2", request.getFactorResultTwo());
							if (longItemContent1 != null && longItemContent2 != null) {
								query.setParameter("longItemContent1", longItemContent1);
								query.setParameter("longItemContent2", longItemContent2);
							}
							if (doubleItemContent1 != null && doubleItemContent2 != null) {
								query.setParameter("bigDecimalItemContent1", doubleItemContent1);
								query.setParameter("bigDecimalItemContent2", doubleItemContent2);
							}
						}
						List list = query.list();
						List<Long> voList;
						if (CommonUtils.isNotEmpty(list)) {
							voList = list;
						} else {
							voList = new ArrayList<>();
						}
						setResult(voList);
					}
				};
		HibernateSessionTemplate.instance().execute(action);
		return action.getResult();
	}

	/**
	 * @param request
	 * @throws
	 * @description: 根据诊断和条件是查询
	 * @return: java.util.List<java.lang.Long>
	 * @author: tongtp
	 * @date: 2020/7/23 19:49
	 */
	public List<Long> queryByDiagnosisAndYes(final QueryByDiagnosisRequest request, final Byte isLoopExecute) {
		HibernateStatelessResultAction<List<Long>> action =
				new AbstractHibernateStatelessResultAction<List<Long>>() {
					@Override
					public void execute(StatelessSession ss) throws Exception {
						StringBuilder sqlBuilder = new StringBuilder();
						sqlBuilder.append("select m.team_chronic_group_id AS teamChronicGroupId from (");
						sqlBuilder.append("select a.team_chronic_group_id,b.report_id from " +
								"fus_team_chronic_group_record a INNER JOIN fus_answer_diagnosis_info b on (a" +
								".person_id" +
								" = b.patient_id OR (a.card_type = b.card_type and a.card_number = b.card_number)) " +
								"and a.chronic_disease_id = b.disease_type_id " +
								"where a.chronic_disease_id = :diseaseTypeId and b.diagnosis_id = :diagnosisId ");
						sqlBuilder.append(" and a.audit_time <= b.answer_time ");
						sqlBuilder.append(") AS  m ");
						//循环执行机制 0 不执行 1 执行
						if (isLoopExecute != null && isLoopExecute == 1) {
							sqlBuilder.append(" LEFT JOIN fus_answer_route_record n " +
									"on m.report_id = n.report_id" +
									" AND n.route_detail_id = :routeDetailId WHERE n.route_detail_id IS NULL");
						} else {
							sqlBuilder.append(" LEFT JOIN follow_up_plan_produce_record n " +
									"on m.team_chronic_group_id = n.team_chronic_group_id" +
									" AND n.route_detail_id = :routeDetailId WHERE n.route_detail_id IS NULL");
						}
						Query query = ss.createSQLQuery(sqlBuilder.toString());
						query.setParameter("diseaseTypeId", request.getDiseaseTypeId());
						query.setParameter("diagnosisId", request.getDiagnosisId());
						query.setParameter("routeDetailId", request.getRouteDetailId());

						List list = query.list();
						List<Long> voList;
						if (CommonUtils.isNotEmpty(list)) {
							voList = list;
						} else {
							voList = new ArrayList<>();
						}
						setResult(voList);
					}
				};
		HibernateSessionTemplate.instance().execute(action);
		return action.getResult();
	}

    /**
     * @param request
     * @throws
     * @description: 分期
     * @return: java.util.List<java.lang.Long>
     * @author: tongtp
     * @date: 2020/7/23 19:49
     */
    public List<Long> queryByExtended(final QueryByExtendedRequest request, final Byte isLoopExecute) {
        HibernateStatelessResultAction<List<Long>> action =
                new AbstractHibernateStatelessResultAction<List<Long>>() {
                    @Override
                    public void execute(StatelessSession ss) throws Exception {
                        StringBuilder sqlBuilder = new StringBuilder();
                        sqlBuilder.append("select m.team_chronic_group_id AS teamChronicGroupId from (");
                        sqlBuilder.append("select a.team_chronic_group_id,b.stages_record_id from " +
                                "fus_team_chronic_group_record a INNER JOIN fus_group_chronic_record_stages_property b on (a" +
                                ".organization_id" +
                                " = b.organization_id ) " +
                                "and a.chronic_disease_id = b.disease_type_id " +
                                "where a.chronic_disease_id = :diseaseTypeId and  b.stages_id = :stagesId ");
                        sqlBuilder.append(" and a.audit_time <=  b.create_time ");
                        sqlBuilder.append(") AS  m ");
                        //循环执行机制 0 不执行 1 执行
//                        if (isLoopExecute != null && isLoopExecute == 1) {
//                            sqlBuilder.append(" LEFT JOIN fus_answer_route_record n " +
//                                    "on m.report_id = n.report_id" +
//                                    " AND n.route_detail_id = :routeDetailId WHERE n.route_detail_id IS NULL");
//                        } else {
						sqlBuilder.append(" LEFT JOIN follow_up_plan_produce_record n " +
                                    "on m.team_chronic_group_id = n.team_chronic_group_id" +
                                    " AND n.route_detail_id = :routeDetailId WHERE n.route_detail_id IS NULL");
//                        }
                        Query query = ss.createSQLQuery(sqlBuilder.toString());
                        query.setParameter("diseaseTypeId", request.getDiseaseTypeId());
                        query.setParameter("stagesId", request.getStagesId());
                        query.setParameter("routeDetailId", request.getRouteDetailId());

                        List list = query.list();
                        List<Long> voList;
                        if (CommonUtils.isNotEmpty(list)) {
                            voList = list;
                        } else {
                            voList = new ArrayList<>();
                        }
                        setResult(voList);
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    /**
     * @param request
     * @throws
     * @description: 拓展属性
     * @return: java.util.List<java.lang.Long>
     * @author: tongtp
     * @date: 2020/7/23 19:49
     */
    public List<Long> queryByStages(final QueryByStagesRequest request, final Byte isLoopExecute) {
        HibernateStatelessResultAction<List<Long>> action =
                new AbstractHibernateStatelessResultAction<List<Long>>() {
                    @Override
                    public void execute(StatelessSession ss) throws Exception {
                        StringBuilder sqlBuilder = new StringBuilder();
                        sqlBuilder.append("select m.team_chronic_group_id AS teamChronicGroupId from (");
                        sqlBuilder.append("select a.team_chronic_group_id,b.group_chronic_record_id from " +
                                "fus_team_chronic_group_record a INNER JOIN fus_group_chronic_record_extend_property b on (a" +
                                ".organization_id" +
                                " = b.organization_id ) " +
                                "and a.chronic_disease_id = b.disease_type_id " +
                                "where a.chronic_disease_id = :diseaseTypeId and  b.extend_id = :extendId ");
                        sqlBuilder.append(" and a.audit_time <=  b.create_time ");
                        sqlBuilder.append(") AS  m ");
                        //循环执行机制 0 不执行 1 执行
//                        if (isLoopExecute != null && isLoopExecute == 1) {
//                            sqlBuilder.append(" LEFT JOIN fus_answer_route_record n " +
//                                    "on m.report_id = n.report_id" +
//                                    " AND n.route_detail_id = :routeDetailId WHERE n.route_detail_id IS NULL");
//                        } else {
                            sqlBuilder.append(" LEFT JOIN follow_up_plan_produce_record n " +
                                    "on m.team_chronic_group_id = n.team_chronic_group_id" +
                                    " AND n.route_detail_id = :routeDetailId WHERE n.route_detail_id IS NULL");
//                        }
                        Query query = ss.createSQLQuery(sqlBuilder.toString());
                        query.setParameter("diseaseTypeId", request.getDiseaseTypeId());
                        query.setParameter("extendId", request.getExtendId());
                        query.setParameter("routeDetailId", request.getRouteDetailId());

                        List list = query.list();
                        List<Long> voList;
                        if (CommonUtils.isNotEmpty(list)) {
                            voList = list;
                        } else {
                            voList = new ArrayList<>();
                        }
                        setResult(voList);
                    }
                };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

	/**
	 * @param request
	 * @throws
	 * @description: 根据诊断和条件否查询
	 * @return: java.util.List<java.lang.Long>
	 * @author: tongtp
	 * @date: 2020/7/23 19:49
	 */
	public List<Long> queryByDiagnosisAndNO(final QueryByDiagnosisRequest request, final Byte isLoopExecute) {
		HibernateStatelessResultAction<List<Long>> action =
				new AbstractHibernateStatelessResultAction<List<Long>>() {
					@Override
					public void execute(StatelessSession ss) throws Exception {
						StringBuilder sqlBuilder = new StringBuilder();
						sqlBuilder.append("select m.team_chronic_group_id AS teamChronicGroupId from (");
						sqlBuilder.append("select c.team_chronic_group_id,d.report_id from  " +
								"fus_team_chronic_group_record c LEFT JOIN ( " +
								" select a.team_chronic_group_id,b.report_id from  " +
								"fus_team_chronic_group_record a INNER JOIN fus_answer_diagnosis_info b" +
								" on (a.person_id " +
								" = b.patient_id OR (a.card_type = b.card_type and a.card_number = b" +
								".card_number))  " +
								"and a.chronic_disease_id = b.disease_type_id  " +
								"where a.chronic_disease_id = :diseaseTypeId AND b.diagnosis_id = :diagnosisId" +
								" and a.audit_time <= b.answer_time ) AS d on c.team_chronic_group_id = d" +
								".team_chronic_group_id where d" +
								".team_chronic_group_id IS NULL AND c.chronic_disease_id = :diseaseTypeId ");
						sqlBuilder.append(") AS  m ");
						//循环执行机制 0 不执行 1 执行
						if (isLoopExecute != null && isLoopExecute == 1) {
							sqlBuilder.append(" LEFT JOIN fus_answer_route_record n " +
									"on m.report_id = n.report_id" +
									" AND n.route_detail_id = :routeDetailId WHERE n.route_detail_id IS NULL");
						} else {
							sqlBuilder.append(" LEFT JOIN follow_up_plan_produce_record n " +
									"on m.team_chronic_group_id = n.team_chronic_group_id" +
									" AND n.route_detail_id = :routeDetailId WHERE n.route_detail_id IS NULL");
						}
						Query query = ss.createSQLQuery(sqlBuilder.toString());
						query.setParameter("diseaseTypeId", request.getDiseaseTypeId());
						query.setParameter("diagnosisId", request.getDiagnosisId());
						query.setParameter("routeDetailId", request.getRouteDetailId());

						List list = query.list();
						List<Long> voList;
						if (CommonUtils.isNotEmpty(list)) {
							voList = list;
						} else {
							voList = new ArrayList<>();
						}
						setResult(voList);
					}
				};
		HibernateSessionTemplate.instance().execute(action);
		return action.getResult();
	}
}
